package com.jieao.template.mapper;

import com.jieao.template.entity.accountMgr.AccountDO;
import com.jieao.template.entity.accountMgr.AdminMenuVO;
import com.jieao.template.entity.accountMgr.AssociatedRoleDTO;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;
import java.util.Map;

/**
 * 账户表(Account)表数据库访问层
 *
 * @author jzw
 * @since 2020-04-28 10:40:54
 */
@Mapper
public interface AccountMapper
{
    /**
     * 获取父菜单
     *
     * @param accountId 账户id
     * @return java.lang.Object
     * @author 姜兆伟
     * @date 2019年12月19日16:20:46
     */
    @Select("SELECT DISTINCT " +
            " a.permission_id, " +
            " a.permission_label, " +
            " a.permission_url ,'${accountId}' accountId " +
            "FROM " +
            " ( " +
            "SELECT " +
            " rp.permission_id , " +
            " p.permission_label, " +
            " p.sort, " +
            " p.permission_url  " +
            "FROM " +
            " role_permission rp " +
            " LEFT JOIN permission p ON p.id = rp.permission_id " +
            " LEFT JOIN account_role ar ON ar.role_id = rp.role_id  " +
            " LEFT JOIN role r ON r.id = ar.role_id  " +
            "WHERE " +
            " p.parent_id = 'A'  " +
            " AND ar.account_id = #{accountId}  " +
            " AND p.`enable` = TRUE  " +
            " AND rp.deleted = FALSE  " +
            " AND p.deleted = FALSE  " +
            " AND ar.deleted = FALSE " +
            " AND (p.permission_type='menu' or p.permission_type='page') " +
            " AND p.display = TRUE  " +
            " AND r.deleted=FALSE " +
            "UNION ALL " +
            "SELECT " +
            " p.id permission_id, " +
            " p.permission_label, " +
            " p.sort, " +
            " p.permission_url  " +
            "FROM " +
            " account_permission ap " +
            " LEFT JOIN permission p ON p.id = ap.permission_id  " +
            "WHERE " +
            " p.parent_id = 'A'  " +
            " AND ap.id = #{accountId}  " +
            " AND p.`enable` = TRUE  " +
            " AND ap.id = FALSE  " +
            " AND p.deleted = FALSE  " +
            " AND (p.permission_type='menu' or p.permission_type='page') " +
            " AND p.display = TRUE  " +
            " ) a order by a.sort")
    @Results({
            @Result(column = "permission_label", property = "permissionLabel"),
            @Result(column = "permission_url", property = "permissionUrl"),
            @Result(
                    column = "{parentId=permission_id,accountId=accountId}",
                    property = "subMenu",
                    many = @Many(
                            select = "com.jieao.template.mapper.AccountMapper.getSubMenuListForAdmin",
                            fetchType = FetchType.DEFAULT
                    )
            )
    })
    List<AdminMenuVO> getMenuList(String accountId);

    /**
     * 获取子菜单
     *
     * @param parentId  父菜单id
     * @param accountId 账户id
     * @author 姜兆伟
     * @date 2019年12月19日16:20:46
     */
    @Select("SELECT DISTINCT " +
            " a.permission_id, " +
            " a.permission_label, " +
            " a.permission_url ,'${accountId}' accountId " +
            "FROM " +
            " ( " +
            "SELECT " +
            " rp.id, " +
            " p.id permission_id, " +
            " p.sort, " +
            " p.permission_label, " +
            " p.permission_url  " +
            "FROM " +
            " role_permission rp " +
            " LEFT JOIN permission p ON p.id = rp.permission_id " +
            " LEFT JOIN account_role ar ON ar.role_id = rp.role_id  " +
            " LEFT JOIN role r ON r.id = ar.role_id  " +
            "WHERE " +
            " p.parent_id = #{parentId}  " +
            " AND (p.permission_type='menu' or p.permission_type='page') " +
            " AND p.display = TRUE  " +
            " AND ar.account_id = #{accountId}  " +
            " AND p.`enable` = TRUE  " +
            " AND rp.deleted = FALSE  " +
            " AND p.deleted = FALSE  " +
            " AND r.deleted = FALSE  " +
            " AND ar.deleted = FALSE UNION ALL " +
            "SELECT " +
            " ap.id, " +
            " p.id permission_id, " +
            " p.sort, " +
            " p.permission_label, " +
            " p.permission_url  " +
            "FROM " +
            " account_permission ap " +
            " LEFT JOIN permission p ON p.id = ap.permission_id  " +
            "WHERE " +
            " p.parent_id = #{parentId}  " +
            " AND (p.permission_type='menu' or p.permission_type='page') " +
            " AND p.display = TRUE  " +
            " AND ap.id = #{accountId}  " +
            " AND p.`enable` = TRUE  " +
            " AND ap.deleted = FALSE  " +
            " AND p.deleted = FALSE" +
            " ) a order by a.sort")
    @Results({
            @Result(column = "permission_label", property = "permissionLabel"),
            @Result(column = "permission_url", property = "permissionUrl"),
            @Result(
                    column = "{parentId=permission_id,accountId=accountId}",
                    property = "subMenu",
                    many = @Many(
                            select = "com.jieao.template.mapper.AccountMapper.getSubMenuListForAdmin",
                            fetchType = FetchType.DEFAULT
                    )
            )
    })
    List<AdminMenuVO> getSubMenuListForAdmin(@Param("parentId") String parentId, @Param("accountId") String accountId);

    /**
     * 获取 用户昵称
     *
     * @param userId 用户id
     * @return String
     * @author Jzw
     * @date 2019/12/19 10:36
     */
    @Select("select nick_name from account where id=#{_parameter}")
    String getUserName(String userId);

    /**
     * 启禁用账户
     *
     * @param accountId 账户id
     * @return java.lang.Object
     * @author Jzw
     * @date 2019/12/13 13:50
     */
    @Update("update account set enable = not enable where id=#{_parameter}")
    int disOrEnaAccount(String accountId);

    /**
     * 移除用户分配的角色
     *
     * @param params
     * @return com.northeastern.university.common.model.JsonData
     * @author Jzw
     * @date 2019/12/30 10:46
     */
    @Insert("update  account_role set deleted=true where account_id=#{accId} ")
    void removeAssociatedRole(AssociatedRoleDTO params);

    /**
     * 给用户分配角色
     *
     * @param params
     * @return com.northeastern.university.common.model.JsonData
     * @author Jzw
     * @date 2019/12/30 10:46
     */
    @Insert("replace into account_role ( role_id, account_id,id) values( #{roleId}, #{accId},#{id} )")
    void associatedRole(AssociatedRoleDTO params);

    /**
     * 移除当前用户所有和权限的关系
     *
     * @param id
     * @return void
     * @author 姜兆伟
     * @date 2019/8/30 15:45
     */
    @Update("update account_permission set deleted=true where account_id=#{id}")
    void removeRolePermission(@Param("id") String id);

    /**
     * 给当前用户分配权限
     *
     * @param dataList
     * @return void
     * @author 姜兆伟
     * @date 2019/8/30 15:36
     */
    @Insert(" <script>replace INTO account_permission( account_id, permission_id,checked,id)" +
            "VALUES" +
            "<foreach collection=\"list\" item=\"item\" separator=\",\">" +
            "(" +
            "#{item.userId},#{item.permissionId},#{item.checked},#{item.id}" +
            ")" +
            "</foreach></script> ")
    void assignmentPermission(List<Map<String, Object>> dataList);

    /**
     * 获取当前用户已选的权限
     *
     * @param id accountId
     * @return java.util.List<java.lang.String>
     * @author Jzw
     * @date 2019/12/17 14:54
     */
    @Select("SELECT " +
            "permission_id " +
            "FROM " +
            "account_permission " +
            "WHERE " +
            "account_id = #{_parameter} " +
            "AND checked = TRUE " +
            "AND deleted = FALSE")
    List<String> listCheckedPermission(String id);

    /**
     * 通过用户名查询单条数据
     *
     * @param name 用户名
     * @return 实例对象
     */
    @Select("select " +
            "id," +
            "account," +
            "nick_name," +
            "email," +
            "phone," +
            "encrypted_password," +
            "salt," +
            "create_time " +
            "from account where deleted =false and enable =true and account = #{_parameter}"
    )
    AccountDO queryByName(String name);

    /**
     * 通过ID查询单条数据
     *
     * @param id 主键
     * @return 实例对象
     */
    @Select("select " +
            "id," +
            "account," +
            "nick_name," +
            "email," +
            "phone," +
            "encrypted_password," +
            "salt," +
            "deleted," +
            "enable," +
            "create_time " +
            "from account where deleted = false and id = #{_parameter}"
    )
    AccountDO queryById(String id);

    /**
     * 查询分页数据
     *
     * @param params 查询筛选条件
     * @return 对象列表
     * @author jzw
     * @since 2020-04-28 10:40:54
     */
    @Select("<script>select " +
            "id," +
            "account," +
            "nick_name," +
            "email," +
            "phone," +
            "encrypted_password," +
            "salt," +
            "deleted," +
            "enable," +
            "create_time," +
            "create_id," +
            "update_time," +
            "update_id " +
            "from account " +
            "where deleted =false " +
            "<if test=\"account != null and account != ''\">" +
            "and account = #{account} " +
            "</if>" +
            "<if test=\"nickName != null and nickName != ''\">" +
            "and nick_name = #{nickName} " +
            "</if>" +
            "<if test=\"email != null and email != ''\">" +
            "and email = #{email} " +
            "</if>" +
            "<if test=\"phone != null and phone != ''\">" +
            "and phone = #{phone} " +
            "</if>" +
            "<if test=\"encryptedPassword != null and encryptedPassword != ''\">" +
            "and encrypted_password = #{encryptedPassword} " +
            "</if>" +
            "<if test=\"salt != null and salt != ''\">" +
            "and salt = #{salt} " +
            "</if>" +
            "<if test=\"enable != null\">" +
            "and enable = #{enable} " +
            "</if>" +
            "<if test=\"createTime != null\">" +
            "and create_time = #{createTime} " +
            "</if>" +
            "<if test=\"createId != null and createId != ''\">" +
            "and create_id = #{createId} " +
            "</if>" +
            "<if test=\"updateTime != null\">" +
            "and update_time = #{updateTime} " +
            "</if>" +
            "<if test=\"updateId != null and updateId != ''\">" +
            "and update_id = #{updateId} " +
            "</if>" +
            "</script>"
    )
    List<AccountDO> queryAllByLimit(AccountDO params);

    /**
     * 查询分页数据总数
     *
     * @param params 查询筛选条件
     * @return 对象列表
     * @author jzw
     * @since 2020-04-28 10:40:54
     */
    @Select("<script>select " +
            "count(id) " +
            "from account " +
            "where deleted =false " +
            "<if test=\"account != null and account != ''\">" +
            "and account = #{account} " +
            "</if>" +
            "<if test=\"nickName != null and nickName != ''\">" +
            "and nick_name = #{nickName} " +
            "</if>" +
            "<if test=\"email != null and email != ''\">" +
            "and email = #{email} " +
            "</if>" +
            "<if test=\"phone != null and phone != ''\">" +
            "and phone = #{phone} " +
            "</if>" +
            "<if test=\"encryptedPassword != null and encryptedPassword != ''\">" +
            "and encrypted_password = #{encryptedPassword} " +
            "</if>" +
            "<if test=\"salt != null and salt != ''\">" +
            "and salt = #{salt} " +
            "</if>" +
            "<if test=\"enable != null\">" +
            "and enable = #{enable} " +
            "</if>" +
            "<if test=\"createTime != null\">" +
            "and create_time = #{createTime} " +
            "</if>" +
            "<if test=\"createId != null and createId != ''\">" +
            "and create_id = #{createId} " +
            "</if>" +
            "<if test=\"updateTime != null\">" +
            "and update_time = #{updateTime} " +
            "</if>" +
            "<if test=\"updateId != null and updateId != ''\">" +
            "and update_id = #{updateId} " +
            "</if>" +
            "</script>"
    )
    int queryAllByLimit_COUNT(AccountDO params);


    /**
     * 通过实体作为筛选条件查询
     *
     * @param account 实例对象
     * @return 对象列表
     * @author jzw
     * @since 2020-04-28 10:40:54
     */
    @Select("<script>select " +
            "id," +
            "account," +
            "nick_name," +
            "email," +
            "phone," +
            "encrypted_password," +
            "salt," +
            "deleted," +
            "enable," +
            "create_time," +
            "create_id," +
            "update_time," +
            "update_id " +
            "from account " +
            "where deleted =false and enable =true " +
            "<if test=\"account != null and account != ''\">" +
            "and account = #{account} " +
            "</if>" +
            "<if test=\"nickName != null and nickName != ''\">" +
            "and nick_name = #{nickName} " +
            "</if>" +
            "<if test=\"email != null and email != ''\">" +
            "and email = #{email} " +
            "</if>" +
            "<if test=\"phone != null and phone != ''\">" +
            "and phone = #{phone} " +
            "</if>" +
            "<if test=\"encryptedPassword != null and encryptedPassword != ''\">" +
            "and encrypted_password = #{encryptedPassword} " +
            "</if>" +
            "<if test=\"salt != null and salt != ''\">" +
            "and salt = #{salt} " +
            "</if>" +
            "<if test=\"enable != null\">" +
            "and enable = #{enable} " +
            "</if>" +
            "<if test=\"createTime != null\">" +
            "and create_time = #{createTime} " +
            "</if>" +
            "<if test=\"createId != null and createId != ''\">" +
            "and create_id = #{createId} " +
            "</if>" +
            "<if test=\"updateTime != null\">" +
            "and update_time = #{updateTime} " +
            "</if>" +
            "<if test=\"updateId != null and updateId != ''\">" +
            "and update_id = #{updateId} " +
            "</if>" +
            "</script>"
    )
    List<AccountDO> queryAll(AccountDO account);

    /**
     * 新增数据
     *
     * @param account 实例对象
     * @author jzw
     * @since 2020-04-28 10:40:54
     */
    @Insert("insert into account(id, account, nick_name, email, phone, encrypted_password, salt, enable, create_time, create_id, update_time, update_id)" +
            " values (#{id}, #{account}, #{nickName}, #{email}, #{phone}, #{encryptedPassword}, #{salt}, #{enable}, now(), #{createId}, #{updateTime}, #{updateId})")
    void insert(AccountDO account);

    /**
     * 修改数据
     *
     * @param account 实例对象
     * @return 影响行数
     * @author jzw
     * @since 2020-04-28 10:40:54
     */
    @Update("<script>update account " +
            "<set>" +
            " <if test=\"account != null and account != ''\">" +
            " account = #{account} , " +
            " </if>" +
            " <if test=\"nickName != null and nickName != ''\">" +
            " nick_name = #{nickName} , " +
            " </if>" +
            " <if test=\"email != null and email != ''\">" +
            " email = #{email} , " +
            " </if>" +
            " <if test=\"phone != null and phone != ''\">" +
            " phone = #{phone} , " +
            " </if>" +
            " <if test=\"encryptedPassword != null and encryptedPassword != ''\">" +
            " encrypted_password = #{encryptedPassword} , " +
            " </if>" +
            " <if test=\"salt != null and salt != ''\">" +
            " salt = #{salt} , " +
            " </if>" +
            " <if test=\"enable != null\">" +
            " enable = #{enable} , " +
            " </if>" +
            " <if test=\"createId != null and createId != ''\">" +
            " create_id = #{createId} , " +
            " </if>" +
            " update_time = now(), " +
            " <if test=\"updateId != null and updateId != ''\">" +
            " update_id = #{updateId}  " +
            " </if>" +
            "</set>" +
            "where deleted = false and id = #{id}</script>")
    int update(AccountDO account);

    /**
     * 通过主键删除数据
     *
     * @param id 主键
     * @return 影响行数
     * @author jzw
     * @since 2020-04-28 10:40:54
     */
    @Delete("update account set deleted = true where id = #{_parameter}")
    int deleteById(String id);

    /**
     * 通过主键集合批量删除数据
     *
     * @param idList 主键集合
     * @author jzw
     * @since 2020-04-28 10:40:54
     */
    @Delete("<script>" +
            "<foreach separator=\";\" collection=\"list\" item=\"item\" >" +
            "update account set deleted =true where id =#{item}" +
            "</foreach></script>")
    void batchDeletionByIdList(List<String> idList);

}